import pymysql
from datetime import datetime

from service.weibo_info import BlogInfo, CommentInfo


class MysqlInfo:
    def __init__(self, user: str, passwd: str, host: str, datebase: str, port: int) -> None:
        self.user = user
        self.passwd = passwd
        self.host = host
        self.datebase = datebase
        self.port = port

    def __str__(self) -> str:
        info = """
        用户昵称：\t%s
        密码：\t\t%s
        主机地址：\t%s
        数据库名称：\t%s
        端口号：\t%d
        """ % (self.user, self.passwd, self.host, self.datebase, self.port)
        return info


class DateBase:

    def __init__(self, mysql: MysqlInfo):
        self.conn = pymysql.connect(
            user=mysql.user,
            passwd=mysql.passwd,
            host=mysql.host,
            database=mysql.datebase,
            port=mysql.port,
        )
        self.cursor = self.conn.cursor()
        self.create_tables()

    def create_tables(self):
        self.cursor.execute("show tables;")
        tables = self.cursor.fetchall()
        blog_exist = False
        for table in tables:
            if "blog" in table:
                blog_exist = True
                print("blog table exist")
        if not blog_exist:
            self.create_blog_table()

        comment_exist = False
        for table in tables:
            if "comment" in table:
                comment_exist = True
                print("comment table exist")
        if not comment_exist:
            self.create_comment_table()

    def create_blog_table(self):
        create_blog_table = """create table blog(
            blog_id BIGINT not null primary key ,
            search_content char(128) not null,
            nick_name char(128) not null,
            user_id BIGINT not null,
            release_time datetime,
            forward_count BIGINT,
            comment_count BIGINT,
            like_count BIGINT,
            content text);"""
        try:
            self.cursor.execute(create_blog_table)
            self.conn.commit()
        except Exception as e:
            print("create_blog_table error: ", e)
            self.conn.rollback()

    def create_comment_table(self):
        create_comment_table = """create table comment(
            comment_id BIGINT not null ,
            father_comment_id BIGINT not null ,
            blog_id BIGINT not null ,
            nick_name char(128) not null ,
            release_time datetime not null ,
            content text);"""
        try:
            self.cursor.execute(create_comment_table)
            self.conn.commit()
        except Exception as e:
            print("create_comment_table error: ", e)
            self.conn.rollback()

    def insert_blog_row(self, blog: BlogInfo) -> bool:
        insert_blog = """insert into blog 
        (blog_id, search_content, nick_name, user_id, release_time, forward_count, comment_count, like_count, content)
        values (%s, %s, %s, %s, %s, %s, %s, %s, %s);"""
        value = (blog.blog_id, blog.search_content, blog.nick_name, blog.user_id, blog.release_time, 
                 blog.forward_count, blog.comment_count, blog.like_count, blog.content)
        try:
            self.cursor.execute(insert_blog, value)
            self.conn.commit()
            return True
        except Exception as e:
            print("insert_blog_row error: ", e)
            self.conn.rollback()
            return False

    def insert_comment_row(self, comment: CommentInfo) -> bool:
        insert_comment = """insert into comment 
        (comment_id, father_comment_id, blog_id, nick_name, release_time, content)
        values (%s, %s, %s, %s, %s, %s);"""
        value = (comment.comment_id, comment.father_comment_id, comment.blog_id, comment.nick_name, 
                 comment.release_time, comment.content)
        try:
            self.cursor.execute(insert_comment, value)
            self.conn.commit()
            return True
        except Exception as e:
            print("insert_comment_row error: ", e)
            self.conn.rollback()
            return False

    def close(self):
        self.cursor.close()
        self.conn.close()


if __name__ == "__main__":
    date = DateBase()
    date.insert_blog_row(BlogInfo(
        "上海疫情", "big_john", 12312312, datetime.now(), 1, 1, 1, "ni hao"
    ))
    date.close()
